package org.jinion.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jinion.constants.OperatorEnum;
import org.jinion.result.AbstractResult;
import org.jinion.result.DefaultResult;

public abstract class AbstractDatabase implements IDatabase {
	private static final Log log = LogFactory.getLog(AbstractDatabase.class);
	protected Connection connection;
	protected List whareClauses;
	
	/**
	 * Class for where clause
	 * @author zhlmmc
	 *
	 */
	protected class WhereClause{
		private String fieldName;
		private Object value;
		private OperatorEnum compareType;
		private OperatorEnum clauseLogicalType;
		
		/**
		 * 
		 * @param fieldName
		 * @param value
		 * @param compareType
		 */
		public WhereClause(String fieldName, Object value, OperatorEnum compareType, OperatorEnum clauseLogicalType){
			this.fieldName = fieldName;
			this.value = value;
			this.compareType = compareType;
			this.clauseLogicalType = clauseLogicalType;
		}

		public OperatorEnum getCompareType() {
			return compareType;
		}

		public String getFieldName() {
			return fieldName;
		}

		public Object getValue() {
			return value;
		}

		public OperatorEnum getClauseLogicalType() {
			return clauseLogicalType;
		}
		
		public void setValue(Object value){
			this.value = value;
		}
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#querySql(java.lang.String)
	 */
	public AbstractResult querySql(String sql) throws SQLException {
		log.debug("Query sql: " + sql);
		ResultSet rs = connection.createStatement().executeQuery(sql);
		//DataSet ds = new DataSet(rs);
		return new DefaultResult(rs);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#querySql(java.lang.String, java.lang.String[])
	 */
	public AbstractResult querySql(String sql, String[] parameters) throws SQLException {
		log.debug("Query sql: " + sql);
		log.debug("Query parameters: " + parameters);
		
		PreparedStatement ps = connection.prepareStatement(sql);
		for (int i = 0; i < parameters.length; i++) {
			ps.setString(i + 1, parameters[i]);
		}
		ResultSet rs = ps.executeQuery();
		//DataSet ds = new DataSet(rs);
		return new DefaultResult(rs);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#executeSql(java.lang.String)
	 */
	public AbstractResult executeSql(String sql) throws SQLException {
		log.debug("Execute sql: " + sql);
		Statement statement = connection.createStatement();
		if(statement.execute(sql)){
			return new DefaultResult(statement.getResultSet());
		} else{
			return new DefaultResult(statement.getUpdateCount());
		}
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#executeSql(java.lang.String, java.lang.String[])
	 */
	public AbstractResult executeSql(String sql, String[] parameters) throws SQLException{
		log.debug("Execute sql: " + sql);
		log.debug("Execute parameters: " + parameters);
		PreparedStatement ps = connection.prepareStatement(sql);
		for (int i = 0; i < parameters.length; i++) {
			ps.setString(i + 1, parameters[i]);
		}
		if (ps.execute()) {
			return new DefaultResult(ps.getResultSet());
		} else{
			return new DefaultResult(ps.getUpdateCount());
		}		
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#executeUpdateSql(java.lang.String)
	 */
	public AbstractResult executeUpdateSql(String sql) throws SQLException {
		log.debug("Execute update sql: " + sql);
		Statement statement = connection.createStatement();
		return new DefaultResult(statement.executeUpdate(sql));
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#executeInsertSql(java.lang.String)
	 */
	public AbstractResult executeInsertSql(String sql) throws SQLException {
		log.debug("Execute insert sql: " + sql);
		Statement statement = connection.createStatement();
		statement.execute(sql);
		// get id of the row
		ResultSet rs = statement.getGeneratedKeys();
		LinkedList list = new LinkedList<Integer>();
		while (rs.next()) {
			int key = rs.getInt(1);
			list.add(key);
		}
		return new DefaultResult(list);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String)
	 */
	public AbstractResult query(String tableName) throws SQLException{
		return this.query(null, tableName, null, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String, java.lang.String)
	 */
	public AbstractResult query(String tableName, String orderBy) throws SQLException{
		return this.query(null, tableName, orderBy, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String, java.lang.String, int)
	 */
	public AbstractResult query(String tableName, String orderBy, OperatorEnum ascOrDesc) throws SQLException{
		return this.query(null, tableName, orderBy, ascOrDesc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String)
	 */
	public AbstractResult query(String[] fieldNames, String tableName) throws SQLException{
		return this.query(fieldNames, tableName, null, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String, java.lang.String)
	 */
	public AbstractResult query(String[] filedNames, String tableName, String orderBy) throws SQLException{
		return this.query(filedNames, tableName, orderBy, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String, java.lang.String, int)
	 */
	public AbstractResult query(String[] filedNames, String tableName, String orderBy, OperatorEnum ascOrDesc) throws SQLException{
		String sql = this.buildQuerySql(new String[]{tableName}, filedNames, orderBy, ascOrDesc);
		return this.querySql(sql);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[])
	 */
	public AbstractResult query(String[] tableNames) throws SQLException{
		return this.query(null, tableNames, null, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String[])
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames) throws SQLException{
		return this.query(fieldNames, tableNames, null, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String[], java.lang.String)
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames, String orderBy) throws SQLException{
		return this.query(fieldNames, tableNames, orderBy, OperatorEnum.asc);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#query(java.lang.String[], java.lang.String[], java.lang.String, int)
	 */
	public AbstractResult query(String[] fieldNames, String[] tableNames, String orderBy, OperatorEnum ascOrDesc) throws SQLException{
		String sql = this.buildQuerySql(tableNames, fieldNames, orderBy, ascOrDesc);
		return this.querySql(sql);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#update(java.lang.String, java.util.Map)
	 */
	public AbstractResult update(String tableName, Map data) throws SQLException{
		return this.update(null, tableName, data);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#update(java.lang.String[], java.lang.String, java.util.Map)
	 */
	public AbstractResult update(String[] fieldNames, String tableName, Map data) throws SQLException{
		String sql = this.buildUpdateSql(new String[]{tableName}, fieldNames, data);
		if (sql != null) {
			return this.executeUpdateSql(sql);
		} else{
			return new DefaultResult(-1);
		}
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#insert(java.lang.String, java.util.Map)
	 */
	public AbstractResult insert(String tableName, Map data) throws SQLException {
		return this.insert(null, tableName, data);
	}

	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#insert(java.lang.String[], java.lang.String, java.util.Map)
	 */
	public AbstractResult insert(String[] fieldNames, String tableName, Map data) throws SQLException{
		String sql = this.buildInsertSql(new String[]{tableName}, fieldNames, data);
		if (sql != null) {
			return this.executeInsertSql(sql);
		} else{
			return null;
		}
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#delete(java.lang.String)
	 */
	public AbstractResult delete(String tableName) throws SQLException  {
		String sql = this.buildDeleteSql(tableName);
		if (sql != null) {
			return this.executeSql(sql);
		} else{
			return null;
		}
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#where(java.lang.String, java.lang.Object)
	 */
	public IDatabase where(String fieldName, Object value){
		return this.where(fieldName, value, OperatorEnum.equal);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#where(java.lang.String, java.lang.Object, int)
	 */
	public IDatabase where(String fieldName, Object value, OperatorEnum compareType) {
		WhereClause wc = new WhereClause(fieldName, value, compareType, OperatorEnum.logicalAnd);
		whareClauses.add(wc);
		return this;
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#orWhere(java.lang.String, java.lang.Object)
	 */
	public IDatabase orWhere(String fieldName, Object value){
		return this.orWhere(fieldName, value, OperatorEnum.equal);
	}
	
	/* (non-Javadoc)
	 * @see org.jinion.IDatabase#orWhere(java.lang.String, java.lang.Object, int)
	 */
	public IDatabase orWhere(String fieldName, Object value, OperatorEnum compareType) {
		//	this clause can not be the first caluse
		if (whareClauses.isEmpty()) {
			throw new UnsupportedOperationException("Missing where caluse! You should call \"where()\" first!");
		}
		WhereClause wc = new WhereClause(fieldName, value, compareType, OperatorEnum.logicalOr);
		whareClauses.add(wc);
		return this;
	}
	
	protected void finalize(){
		// free resources
		try {
			this.connection.close();
			log.debug("Connection of Database " + this.toString() + " closed.");
		} catch (SQLException e) {
			log.debug(e.getMessage());
		}
	}
	
	/**
	 * Query sql is a little complicated, so separate it with others. 
	 * 
	 * @param sqlType
	 * @param tableNames
	 * @param filedNames
	 * @param Data
	 * @param orderBy
	 * @param ascOrDesc
	 * @return
	 * @throws SQLException
	 */
	protected abstract String buildQuerySql(String[] tableNames, String[] fieldNames, String orderBy, OperatorEnum ascOrDesc) throws SQLException;
	
	/**
	 * 
	 * @param tableNames
	 * @param filedNames
	 * @param Data
	 * @return
	 * @throws SQLException
	 */
	protected abstract String buildUpdateSql(String[] tableNames, String[] fieldNames, Object Data) throws SQLException;
	
	/**
	 * 
	 * @param tableNames
	 * @param filedNames
	 * @param Data
	 * @return
	 * @throws SQLException
	 */
	protected abstract String buildInsertSql(String[] tableNames, String[] fieldNames, Object Data) throws SQLException;
	
	/**
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	protected abstract String buildDeleteSql(String tableName) throws SQLException;
	
}
